import akshare as ak
import psycopg2
from psycopg2 import sql
import time
import random
from datetime import datetime
import logging
import pandas as pd

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('stock_financial_analysis.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'port': '5432',
    'database': 'stock_db',
    'user': 'leizhen',
    'password': '751982leizhen'
}

def get_db_connection():
    """获取数据库连接"""
    try:
        return psycopg2.connect(**DB_CONFIG)
    except Exception as e:
        logger.error(f"数据库连接失败: {e}")
        raise

def create_table_if_not_exists(conn):
    """创建财务指标表"""
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS stock_financial_analysis_indicator (
        stock_code VARCHAR(10) NOT NULL,
        date DATE NOT NULL,
        diluted_eps NUMERIC(20,4),
        weighted_eps NUMERIC(20,4),
        adjusted_eps NUMERIC(20,4),
        deducted_eps NUMERIC(20,4),
        pre_adjusted_bps NUMERIC(20,4),
        adjusted_bps NUMERIC(20,4),
        operating_cash_flow_per_share NUMERIC(20,4),
        capital_reserve_per_share NUMERIC(20,4),
        undistributed_profit_per_share NUMERIC(20,4),
        adjusted_net_assets_per_share NUMERIC(20,4),
        total_assets_profit_ratio NUMERIC(20,4),
        main_business_profit_ratio NUMERIC(20,4),
        net_profit_margin_on_total_assets NUMERIC(20,4),
        cost_expense_profit_ratio NUMERIC(20,4),
        operating_profit_ratio NUMERIC(20,4),
        main_business_cost_ratio NUMERIC(20,4),
        net_profit_margin_on_sales NUMERIC(20,4),
        return_on_equity NUMERIC(20,4),
        return_on_net_assets NUMERIC(20,4),
        return_on_assets NUMERIC(20,4),
        gross_profit_margin NUMERIC(20,4),
        three_expense_ratio NUMERIC(20,4),
        non_main_business_ratio NUMERIC(20,4),
        main_business_profit_ratio_2 NUMERIC(20,4),
        dividend_payout_ratio NUMERIC(20,4),
        investment_return_ratio NUMERIC(20,4),
        main_business_profit NUMERIC(20,4),
        roe NUMERIC(20,4),
        weighted_roe NUMERIC(20,4),
        net_profit_after_deduction NUMERIC(20,4),
        main_business_income_growth_rate NUMERIC(20,4),
        net_profit_growth_rate NUMERIC(20,4),
        net_assets_growth_rate NUMERIC(20,4),
        total_assets_growth_rate NUMERIC(20,4),
        accounts_receivable_turnover NUMERIC(20,4),
        accounts_receivable_turnover_days NUMERIC(20,4),
        inventory_turnover_days NUMERIC(20,4),
        inventory_turnover NUMERIC(20,4),
        fixed_assets_turnover NUMERIC(20,4),
        total_assets_turnover NUMERIC(20,4),
        total_assets_turnover_days NUMERIC(20,4),
        current_assets_turnover NUMERIC(20,4),
        current_assets_turnover_days NUMERIC(20,4),
        shareholders_equity_turnover NUMERIC(20,4),
        current_ratio NUMERIC(20,4),
        quick_ratio NUMERIC(20,4),
        cash_ratio NUMERIC(20,4),
        interest_coverage_ratio NUMERIC(20,4),
        long_term_debt_to_working_capital_ratio NUMERIC(20,4),
        shareholders_equity_ratio NUMERIC(20,4),
        long_term_liability_ratio NUMERIC(20,4),
        shareholders_equity_to_fixed_assets_ratio NUMERIC(20,4),
        debt_to_equity_ratio NUMERIC(20,4),
        long_term_assets_to_long_term_funds_ratio NUMERIC(20,4),
        capitalization_ratio NUMERIC(20,4),
        net_fixed_assets_ratio NUMERIC(20,4),
        capital_immobilization_ratio NUMERIC(20,4),
        equity_ratio NUMERIC(20,4),
        liquidation_value_ratio NUMERIC(20,4),
        fixed_assets_ratio NUMERIC(20,4),
        asset_liability_ratio NUMERIC(20,4),
        total_assets NUMERIC(20,4),
        operating_cash_flow_to_sales_ratio NUMERIC(20,4),
        operating_cash_flow_return_on_assets NUMERIC(20,4),
        operating_cash_flow_to_net_profit_ratio NUMERIC(20,4),
        operating_cash_flow_to_liabilities_ratio NUMERIC(20,4),
        cash_flow_ratio NUMERIC(20,4),
        short_term_stock_investment NUMERIC(20,4),
        short_term_bond_investment NUMERIC(20,4),
        short_term_other_operating_investment NUMERIC(20,4),
        long_term_stock_investment NUMERIC(20,4),
        long_term_bond_investment NUMERIC(20,4),
        long_term_other_operating_investment NUMERIC(20,4),
        accounts_receivable_within_1_year NUMERIC(20,4),
        accounts_receivable_1_to_2_years NUMERIC(20,4),
        accounts_receivable_2_to_3_years NUMERIC(20,4),
        accounts_receivable_over_3_years NUMERIC(20,4),
        prepayment_within_1_year NUMERIC(20,4),
        prepayment_1_to_2_years NUMERIC(20,4),
        prepayment_2_to_3_years NUMERIC(20,4),
        prepayment_over_3_years NUMERIC(20,4),
        other_receivables_within_1_year NUMERIC(20,4),
        other_receivables_1_to_2_years NUMERIC(20,4),
        other_receivables_2_to_3_years NUMERIC(20,4),
        other_receivables_over_3_years NUMERIC(20,4),
        PRIMARY KEY (stock_code, date)
    );
    """
    try:
        with conn.cursor() as cursor:
            cursor.execute(create_table_sql)
            conn.commit()
            logger.info("财务指标表创建/检查完成")
    except Exception as e:
        conn.rollback()
        logger.error(f"创建表失败: {e}")
        raise

def get_stock_list(conn):
    """从stocks表获取股票列表"""
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT symbol FROM stocks;")
            return [row[0] for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"获取股票列表失败: {e}")
        raise

def get_financial_data(conn, stock_code, start_year="2019"):
    """获取股票财务指标数据"""
    # 先检查数据库中是否已有该股票的数据
    with conn.cursor() as cursor:
        cursor.execute("""
            SELECT EXISTS (
                SELECT 1 FROM stock_financial_analysis_indicator 
                WHERE stock_code = %s
            );
        """, (stock_code,))
        if cursor.fetchone()[0]:
            logger.info(f"股票{stock_code}数据已存在，跳过获取")
            return None
    
    max_retries = 3
    for attempt in range(max_retries):
        try:
            df = ak.stock_financial_analysis_indicator(symbol=stock_code, start_year=start_year)
            if df is not None and not df.empty:
                # 打印调试信息
                logger.info(f"获取到股票{stock_code}数据，列名: {df.columns.tolist()}")
                logger.info(f"首行数据样例: {df.iloc[0].to_dict()}")
                
                # 确保股票代码为字符串并去除可能存在的空格和特殊字符
                df['stock_code'] = str(stock_code).strip()
                logger.info(f"股票代码处理结果: {df['stock_code'].iloc[0]} (类型: {type(df['stock_code'].iloc[0])})")
                return df
            return None
        except Exception as e:
            if attempt == max_retries - 1:
                logger.error(f"获取股票{stock_code}财务数据失败: {e}")
                return None
            time.sleep(2 ** attempt)  # 指数退避

def save_financial_data(conn, stock_code, df):
    """保存财务指标数据到数据库"""
    if df is None or df.empty:
        logger.info(f"股票{stock_code}无有效财务数据")
        return
    
    # 中英文列名映射表
    column_mapping = {
        '摊薄每股收益(元)': 'diluted_eps',
        '加权每股收益(元)': 'weighted_eps',
        '每股收益_调整后(元)': 'adjusted_eps',
        '扣除非经常性损益后的每股收益(元)': 'deducted_eps',
        '每股净资产_调整前(元)': 'pre_adjusted_bps',
        '每股净资产_调整后(元)': 'adjusted_bps',
        '每股经营性现金流(元)': 'operating_cash_flow_per_share',
        '每股资本公积金(元)': 'capital_reserve_per_share',
        '每股未分配利润(元)': 'undistributed_profit_per_share',
        '调整后的每股净资产(元)': 'adjusted_net_assets_per_share',
        '总资产利润率(%)': 'total_assets_profit_ratio',
        '主营业务利润率(%)': 'main_business_profit_ratio',
        '总资产净利润率(%)': 'net_profit_margin_on_total_assets',
        '成本费用利润率(%)': 'cost_expense_profit_ratio',
        '营业利润率(%)': 'operating_profit_ratio',
        '主营业务成本率(%)': 'main_business_cost_ratio',
        '销售净利率(%)': 'net_profit_margin_on_sales',
        '股本报酬率(%)': 'return_on_equity',
        '净资产报酬率(%)': 'return_on_net_assets',
        '资产报酬率(%)': 'return_on_assets',
        '销售毛利率(%)': 'gross_profit_margin',
        '三项费用比重': 'three_expense_ratio',
        '非主营比重': 'non_main_business_ratio',
        '主营利润比重': 'main_business_profit_ratio_2',
        '股息发放率(%)': 'dividend_payout_ratio',
        '投资收益率(%)': 'investment_return_ratio',
        '主营业务利润(元)': 'main_business_profit',
        '净资产收益率(%)': 'roe',
        '加权净资产收益率(%)': 'weighted_roe',
        '扣除非经常性损益后的净利润(元)': 'net_profit_after_deduction',
        '主营业务收入增长率(%)': 'main_business_income_growth_rate',
        '净利润增长率(%)': 'net_profit_growth_rate',
        '净资产增长率(%)': 'net_assets_growth_rate',
        '总资产增长率(%)': 'total_assets_growth_rate',
        '应收账款周转率(次)': 'accounts_receivable_turnover',
        '应收账款周转天数(天)': 'accounts_receivable_turnover_days',
        '存货周转天数(天)': 'inventory_turnover_days',
        '存货周转率(次)': 'inventory_turnover',
        '固定资产周转率(次)': 'fixed_assets_turnover',
        '总资产周转率(次)': 'total_assets_turnover',
        '总资产周转天数(天)': 'total_assets_turnover_days',
        '流动资产周转率(次)': 'current_assets_turnover',
        '流动资产周转天数(天)': 'current_assets_turnover_days',
        '股东权益周转率(次)': 'shareholders_equity_turnover',
        '流动比率': 'current_ratio',
        '速动比率': 'quick_ratio',
        '现金比率(%)': 'cash_ratio',
        '利息支付倍数': 'interest_coverage_ratio',
        '长期债务与营运资金比率(%)': 'long_term_debt_to_working_capital_ratio',
        '股东权益比率(%)': 'shareholders_equity_ratio',
        '长期负债比率(%)': 'long_term_liability_ratio',
        '股东权益与固定资产比率(%)': 'shareholders_equity_to_fixed_assets_ratio',
        '负债与所有者权益比率(%)': 'debt_to_equity_ratio',
        '长期资产与长期资金比率(%)': 'long_term_assets_to_long_term_funds_ratio',
        '资本化比率(%)': 'capitalization_ratio',
        '固定资产净值率(%)': 'net_fixed_assets_ratio',
        '资本固定化比率(%)': 'capital_immobilization_ratio',
        '产权比率(%)': 'equity_ratio',
        '清算价值比率(%)': 'liquidation_value_ratio',
        '固定资产比重(%)': 'fixed_assets_ratio',
        '资产负债率(%)': 'asset_liability_ratio',
        '总资产(元)': 'total_assets',
        '经营现金净流量对销售收入比率(%)': 'operating_cash_flow_to_sales_ratio',
        '资产的经营现金流量回报率(%)': 'operating_cash_flow_return_on_assets',
        '经营现金净流量与净利润的比率(%)': 'operating_cash_flow_to_net_profit_ratio',
        '经营现金净流量对负债比率(%)': 'operating_cash_flow_to_liabilities_ratio',
        '现金流量比率(%)': 'cash_flow_ratio',
        '短期股票投资(元)': 'short_term_stock_investment',
        '短期债券投资(元)': 'short_term_bond_investment',
        '短期其它经营性投资(元)': 'short_term_other_operating_investment',
        '长期股票投资(元)': 'long_term_stock_investment',
        '长期债券投资(元)': 'long_term_bond_investment',
        '长期其它经营性投资(元)': 'long_term_other_operating_investment',
        '1年以内应收帐款(元)': 'accounts_receivable_within_1_year',
        '1-2年以内应收帐款(元)': 'accounts_receivable_1_to_2_years',
        '2-3年以内应收帐款(元)': 'accounts_receivable_2_to_3_years',
        '3年以内应收帐款(元)': 'accounts_receivable_over_3_years',
        '1年以内预付货款(元)': 'prepayment_within_1_year',
        '1-2年以内预付货款(元)': 'prepayment_1_to_2_years',
        '2-3年以内预付货款(元)': 'prepayment_2_to_3_years',
        '3年以内预付货款(元)': 'prepayment_over_3_years',
        '1年以内其它应收款(元)': 'other_receivables_within_1_year',
        '1-2年以内其它应收款(元)': 'other_receivables_1_to_2_years',
        '2-3年以内其它应收款(元)': 'other_receivables_2_to_3_years',
        '3年以内其它应收款(元)': 'other_receivables_over_3_years'
    }

    try:
        with conn.cursor() as cursor:
            # 准备插入SQL
            columns = [
                'stock_code', 'date', 'diluted_eps', 'weighted_eps', 'adjusted_eps', 'deducted_eps',
                'pre_adjusted_bps', 'adjusted_bps', 'operating_cash_flow_per_share',
                'capital_reserve_per_share', 'undistributed_profit_per_share',
                'adjusted_net_assets_per_share', 'total_assets_profit_ratio',
                'main_business_profit_ratio', 'net_profit_margin_on_total_assets',
                'cost_expense_profit_ratio', 'operating_profit_ratio',
                'main_business_cost_ratio', 'net_profit_margin_on_sales',
                'return_on_equity', 'return_on_net_assets', 'return_on_assets',
                'gross_profit_margin', 'three_expense_ratio', 'non_main_business_ratio',
                'main_business_profit_ratio_2', 'dividend_payout_ratio',
                'investment_return_ratio', 'main_business_profit', 'roe', 'weighted_roe',
                'net_profit_after_deduction', 'main_business_income_growth_rate',
                'net_profit_growth_rate', 'net_assets_growth_rate',
                'total_assets_growth_rate', 'accounts_receivable_turnover',
                'accounts_receivable_turnover_days', 'inventory_turnover_days',
                'inventory_turnover', 'fixed_assets_turnover', 'total_assets_turnover',
                'total_assets_turnover_days', 'current_assets_turnover',
                'current_assets_turnover_days', 'shareholders_equity_turnover',
                'current_ratio', 'quick_ratio', 'cash_ratio', 'interest_coverage_ratio',
                'long_term_debt_to_working_capital_ratio', 'shareholders_equity_ratio',
                'long_term_liability_ratio', 'shareholders_equity_to_fixed_assets_ratio',
                'debt_to_equity_ratio', 'long_term_assets_to_long_term_funds_ratio',
                'capitalization_ratio', 'net_fixed_assets_ratio',
                'capital_immobilization_ratio', 'equity_ratio', 'liquidation_value_ratio',
                'fixed_assets_ratio', 'asset_liability_ratio', 'total_assets',
                'operating_cash_flow_to_sales_ratio', 'operating_cash_flow_return_on_assets',
                'operating_cash_flow_to_net_profit_ratio', 'operating_cash_flow_to_liabilities_ratio',
                'cash_flow_ratio', 'short_term_stock_investment', 'short_term_bond_investment',
                'short_term_other_operating_investment', 'long_term_stock_investment',
                'long_term_bond_investment', 'long_term_other_operating_investment',
                'accounts_receivable_within_1_year', 'accounts_receivable_1_to_2_years',
                'accounts_receivable_2_to_3_years', 'accounts_receivable_over_3_years',
                'prepayment_within_1_year', 'prepayment_1_to_2_years',
                'prepayment_2_to_3_years', 'prepayment_over_3_years',
                'other_receivables_within_1_year', 'other_receivables_1_to_2_years',
                'other_receivables_2_to_3_years', 'other_receivables_over_3_years'
            ]
            
            # 构建INSERT语句
            # 构建UPDATE部分
            update_parts = [
                sql.SQL("{} = EXCLUDED.{}").format(
                    sql.Identifier(col), sql.Identifier(col))
                for col in columns if col not in ['stock_code', 'date']
            ]
            
            insert_sql = sql.SQL("""
                INSERT INTO stock_financial_analysis_indicator ({})
                VALUES ({})
                ON CONFLICT (stock_code, date) DO UPDATE SET
                    {}
            """).format(
                sql.SQL(', ').join(map(sql.Identifier, columns)),
                sql.SQL(', ').join([sql.Placeholder()] * len(columns)),
                sql.SQL(', ').join(update_parts)
            )
            
            # 准备数据
            data = []
            for _, row in df.iterrows():
                row_data = [str(stock_code).strip()]  # 首先添加stock_code
                
                # 处理日期字段
                date_str = None
                for date_col in ['日期', 'date', '报告日期', 'report_date']:
                    if date_col in row:
                        date_str = str(row[date_col])
                        break
                if date_str:
                    try:
                        row_data.append(datetime.strptime(date_str, '%Y-%m-%d').date())
                    except ValueError:
                        row_data.append(None)
                        logger.warning(f"无法解析日期: {date_str}")
                else:
                    row_data.append(None)
                    logger.warning("未找到日期字段")
                
                # 处理其他字段
                for col in columns[2:]:  # 跳过前两个字段(stock_code和date)
                            
                        # 查找对应的中文列名
                        chinese_col = None
                        for k, v in column_mapping.items():
                            if v == col:
                                chinese_col = k
                                break
                        
                        if chinese_col and chinese_col in row:
                            val = row[chinese_col]
                            if pd.isna(val):
                                row_data.append(None)
                            else:
                                try:
                                    # 尝试转换为float
                                    row_data.append(float(val))
                                except (ValueError, TypeError):
                                    row_data.append(None)
                                    logger.warning(f"无法转换值: {val} (列: {col})")
                        else:
                            row_data.append(None)
                            if chinese_col:
                                logger.debug(f"未找到匹配的列: {chinese_col} (对应数据库字段: {col})")
                # 确保数据项数与列数匹配
                if len(row_data) != len(columns):
                    logger.error(f"数据项数不匹配: 预期{len(columns)}项, 实际{len(row_data)}项")
                    logger.debug(f"问题行数据: {row_data}")
                    continue
                
                data.append(tuple(row_data))
            
            # 执行批量插入
            if data:
                logger.debug(f"准备插入的数据样例: {data[0]}")
                logger.debug(f"SQL语句: {cursor.mogrify(insert_sql, data[0])}")
                cursor.executemany(insert_sql, data)
            conn.commit()
            logger.info(f"成功保存股票{stock_code}的{len(data)}条财务指标数据")
            
    except Exception as e:
        conn.rollback()
        logger.error(f"保存股票{stock_code}财务数据失败: {e}")
        raise

def main():
    conn = None
    try:
        conn = get_db_connection()
        create_table_if_not_exists(conn)
        
        stock_codes = get_stock_list(conn)
        if not stock_codes:
            logger.error("未获取到股票列表")
            return
        
        total = len(stock_codes)
        success = 0
        for i, code in enumerate(stock_codes, 1):
            logger.info(f"正在处理 [{i}/{total}] {code}")
            try:
                df = get_financial_data(conn, code)
                if df is not None:  # 只有当获取到新数据时才保存
                    save_financial_data(conn, code, df)
                    success += 1
                    time.sleep(random.randint(5,10))  # 只在获取新数据后控制请求频率
            except Exception as e:
                logger.error(f"处理股票{code}时出错: {e}")
                continue
        
        logger.info(f"财务指标采集完成，共处理{success}/{total}只股票")
        
    except Exception as e:
        logger.error(f"程序运行失败: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    main()
